﻿ALTER TABLE dbo.[Order] ADD BoundOrderId [int] NULL
CREATE NONCLUSTERED INDEX [IX_BoundOrderId] ON [dbo].[Order]([BoundOrderId] ASC);

ALTER TABLE dbo.Shipment ADD PackageId [int] NULL
CREATE NONCLUSTERED INDEX [IX_PackageId] ON [dbo].[Shipment](PackageId ASC);

ALTER TABLE dbo.[Order] ADD DistrictId [int] NOT NULL DEFAULT (1)
CREATE NONCLUSTERED INDEX [IX_DistrictId] ON [dbo].[Order]([DistrictId] ASC);


ALTER TABLE dbo.[Invoice] ADD DistrictId [int] NOT NULL DEFAULT (1)
CREATE NONCLUSTERED INDEX [IX_DistrictId] ON [dbo].[Invoice]([DistrictId] ASC);

ALTER TABLE dbo.[Invoice] ADD OrderKindId [int] NOT NULL DEFAULT (10)
CREATE NONCLUSTERED INDEX [IX_OrderKindId] ON [dbo].[Invoice]([OrderKindId] ASC);

ALTER TABLE dbo.[Category] ADD IncludeInTopMenu [BIT] NOT NULL DEFAULT (1)

UPDATE dbo.[Customer] SET TimeZoneId = 'Pacific Standard Time' WHERE Deleted = 0 AND IsSystemAccount = 0 AND Email IS NOT NULL

ALTER TABLE dbo.[LogisticsDistrict] ADD Code NVARCHAR (15) NOT NULL DEFAULT ('VAN')

CREATE TABLE [dbo].[SurchargeType] (
    [Id]                  INT            IDENTITY (1, 1) NOT NULL,
    [Name]                NVARCHAR (MAX) NOT NULL,
    [SystemName]          NVARCHAR (MAX) NOT NULL,
    [IsFixedCharge]       BIT            NOT NULL,
	[DisplayOrder]        INT            NOT NULL,
	PRIMARY KEY CLUSTERED ([Id] ASC),
);

CREATE TABLE [dbo].[Surcharge] (
    [Id]                  INT             IDENTITY (1, 1) NOT NULL,
    [SurchargeTypeId]     INT             NOT NULL,
	[OrderId]             INT             NOT NULL,
	[PercentageOrFee]     DECIMAL (18, 4) NOT NULL,
    [Value]               DECIMAL (18, 4) NOT NULL,
    [Zip]                 NVARCHAR (MAX)  NULL,
    [StateProvinceId]     INT             NULL,
    [CountryId]           INT             NULL,
	PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [Surcharge_SurchargeType] FOREIGN KEY ([SurchargeTypeId]) REFERENCES [dbo].[SurchargeType] ([Id]),
    CONSTRAINT [Surcharge_Order] FOREIGN KEY ([OrderId]) REFERENCES [dbo].[Order] ([Id]) ON DELETE CASCADE,
);

GO
CREATE NONCLUSTERED INDEX [IX_SurchargeType_DisplayOrder]
    ON [dbo].[SurchargeType]([DisplayOrder] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_Surcharge_SurchargeTypeId]
    ON [dbo].[Surcharge]([SurchargeTypeId] ASC);
CREATE NONCLUSTERED INDEX [IX_Surcharge_OrderId]
    ON [dbo].[Surcharge]([OrderId] ASC);
CREATE NONCLUSTERED INDEX [IX_Surcharge_StateProvinceId]
    ON [dbo].[Surcharge]([StateProvinceId] ASC);
CREATE NONCLUSTERED INDEX [IX_Surcharge_CountryId]
    ON [dbo].[Surcharge]([CountryId] ASC);

Truncate table dbo.TaxRate
ALTER TABLE dbo.TaxRate ADD SurchargeTypeId [int] NOT NULL
ALTER TABLE dbo.TaxRate ADD IsFixedCharge [BIT] NOT NULL
CREATE NONCLUSTERED INDEX [IX_SurchargeTypeId] ON [dbo].[TaxRate](SurchargeTypeId ASC);
CREATE NONCLUSTERED INDEX [IX_TaxCategoryId] ON [dbo].[TaxRate](TaxCategoryId ASC);
CREATE NONCLUSTERED INDEX [IX_CountryId] ON [dbo].[TaxRate](CountryId ASC);
CREATE NONCLUSTERED INDEX [IX_StateProvinceId] ON [dbo].[TaxRate](StateProvinceId ASC);

EXEC sp_rename 'dbo.TaxRate.Percentage', 'PercentageOrFee', 'COLUMN'; 

ALTER TABLE dbo.[LogisticsDistrict] ADD TimeZoneId NVARCHAR (MAX) NULL DEFAULT ('Pacific Standard Time')
ALTER TABLE dbo.[Invoice] ADD TimeZoneId NVARCHAR (MAX) NULL DEFAULT ('Pacific Standard Time')
GO
UPDATE dbo.[Invoice] SET TimeZoneId = 'Pacific Standard Time'
GO

UPDATE dbo.[StateProvince] SET Published = 1 WHERE Id = 1 OR Id = 2
GO

SET IDENTITY_INSERT [dbo].[Region] ON
INSERT INTO [dbo].[Region] ([Id], [Name], [DisplayOrder]) VALUES (2, N'Calgary', 1)
SET IDENTITY_INSERT [dbo].[Region] OFF
GO

UPDATE dbo.LogisticsDistrict SET TimeZoneId = N'Pacific Standard Time' WHERE id = 1

SET IDENTITY_INSERT [dbo].[LogisticsDistrict] ON
INSERT INTO [dbo].[LogisticsDistrict] ([Id], [RegionId], [StateProvinceId], [Name], [DisplayOrder], [CountryCode], [Code], [TimeZoneId]) VALUES (2, 2, 1, N'Calgary', 1, N'CA', N'CAL', N'Mountain Standard Time')
SET IDENTITY_INSERT [dbo].[LogisticsDistrict] OFF
GO

TRUNCATE TABLE dbo.[LogisticsZone]
GO
SET IDENTITY_INSERT [dbo].[LogisticsZone] ON
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (1, N'V7W|V7S|V7V|V7T|V7R|V7P|V7N|V7M|V7K|V7L|V7J|V7H|V7G', N'00101', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (2, N'V6G|V6E|V6Z|V6C|V6B|V6A|V5L|V5K|V7X|V7Y', N'00102', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (3, N'V6T|V6S|V6R|V6K|V6L|V6N|V6M|V6J|V6H|V5M|V5N|V5R|V5T|V5W|V5V|V5Y|V5Z', N'00103', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (4, N'V5P|V5S|V5X|V6P|V6Y|V6X|V7A|V7B|V7C|V7E', N'00104', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (5, N'V3M|V4G|V4L|V4M|V4K|V6V|V6W', N'00105', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (6, N'V5A|V5B|V5C|V5E|V5G|V5H|V5J|V3L|V3N', N'00106', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (7, N'V3B|V3C|V3E|V3H|V3J|V3K', N'00107', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (8, N'V2W|V2X|V3Y|V4R', N'00108', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (9, N'V3R|V3S|V3T|V3V|V3W|V3X|V4A|V4B|V4C|V4E|V4P', N'00109', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (10, N'V1M|V2Y|V2Z|V3A|V4N|V4W', N'00110', 1, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (11, N'V2T|V2R|V2S|V2V|V3G|V4X|V2P|V4Z|V4S', N'00111', 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (22, N'T5L|T5M|T5N|T5P|T5S|T5V|T5X|T6V', N'00301', 4, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (23, N'T5A|T5B|T5C|T5E|T5J|T5H|T5K|T5G|T5W|T5V|T5Y|T5Z', N'00302', 4, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (24, N'T5T|T5R|T6G|T6H|T6J|T6R|T6M|T6W', N'00303', 4, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (25, N'T6A|T6B|T6C|T6E|T6L|T6N|T6K|T6P|T6T|T6X', N'00304', 4, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (12, N'T3G|T3L|T3A|T3B|T3K|T2K|T2L|T2M|T2N|T3H', N'00201', 2, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (13, N'T3N|T3J|T2E|T1Y|T2A', N'00202', 2, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (14, N'T3C|T2P|T2R|T3E|T2T|T2S|T2V|T2W|T2Y', N'00203', 2, NULL)
INSERT INTO [dbo].[LogisticsZone] ([Id], [ZipPostalCode], [Name], [DistrictId], [ZoneGroupId]) VALUES (15, N'T2G|T2B|T2H|T2C|T2J|T2Z|T2X|T3M', N'00204', 2, NULL)
SET IDENTITY_INSERT [dbo].[LogisticsZone] OFF
GO